const mysql = require('mysql2/promise')
const bcrypt = require('bcryptjs')

async function testAndInitDatabase () {
  console.log('🔍 测试数据库连接...')

  let connection

  try {
    // 创建数据库连接
    connection = await mysql.createConnection({
      host: 'localhost',
      port: 3306,
      user: 'root',
      password: 'GKL9512357',
      charset: 'utf8mb4'
    })

    console.log('✅ 数据库连接成功')

    // 创建数据库
    await connection.query('CREATE DATABASE IF NOT EXISTS novel_platform')
    console.log('✅ 数据库 novel_platform 创建成功')

    // 关闭连接并重新连接到目标数据库
    await connection.end()

    connection = await mysql.createConnection({
      host: 'localhost',
      port: 3306,
      user: 'root',
      password: 'GKL9512357',
      database: 'novel_platform',
      charset: 'utf8mb4'
    })

    // 创建用户表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL,
        username VARCHAR(100) NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        nickname VARCHAR(100) DEFAULT NULL,
        avatar_url VARCHAR(500) DEFAULT NULL,
        role ENUM('user', 'admin', 'moderator') DEFAULT 'user',
        is_vip BOOLEAN DEFAULT FALSE,
        vip_expire_date DATETIME DEFAULT NULL,
        coin_balance INT DEFAULT 0,
        reading_time INT DEFAULT 0,
        level INT DEFAULT 1,
        status VARCHAR(20) DEFAULT 'active',
        last_login_time DATETIME NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_username (username),
        INDEX idx_role (role)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    console.log('✅ 用户表创建成功')

    // 检查是否已有用户数据
    const [rows] = await connection.execute(
      'SELECT COUNT(*) as count FROM users'
    )
    if (rows[0].count > 0) {
      console.log(`📊 数据库中已有 ${rows[0].count} 个用户`)

      // 显示现有用户
      const [users] = await connection.execute(
        'SELECT id, email, username, role, is_vip, created_at FROM users LIMIT 10'
      )
      console.log('\n📋 现有用户列表:')
      users.forEach(user => {
        console.log(
          `  ID: ${user.id}, Email: ${user.email}, 用户名: ${
            user.username
          }, 角色: ${user.role}, VIP: ${user.is_vip ? '是' : '否'}`
        )
      })
      return
    }

    console.log('📝 添加测试用户数据...')

    // 测试用户数据
    const testUsers = [
      {
        email: 'admin@novel.com',
        username: '管理员',
        password: 'admin123',
        role: 'admin',
        is_vip: true,
        coin_balance: 1000,
        level: 10
      },
      {
        email: 'user1@test.com',
        username: '测试用户1',
        password: 'password123',
        role: 'user',
        is_vip: false,
        coin_balance: 100,
        level: 2
      },
      {
        email: 'user2@test.com',
        username: '测试用户2',
        password: 'password123',
        role: 'user',
        is_vip: true,
        coin_balance: 200,
        level: 5
      },
      {
        email: 'vip@novel.com',
        username: 'VIP用户',
        password: 'vip123',
        role: 'user',
        is_vip: true,
        coin_balance: 500,
        level: 8,
        vip_expire_date: '2025-12-31 23:59:59'
      },
      {
        email: 'moderator@novel.com',
        username: '版主',
        password: 'mod123',
        role: 'moderator',
        is_vip: true,
        coin_balance: 300,
        level: 6
      }
    ]

    for (const user of testUsers) {
      try {
        // 加密密码
        const hashedPassword = await bcrypt.hash(user.password, 12)

        // 插入用户
        await connection.execute(
          `INSERT INTO users (email, username, password_hash, role, is_vip, coin_balance, level, vip_expire_date)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            user.email,
            user.username,
            hashedPassword,
            user.role,
            user.is_vip,
            user.coin_balance,
            user.level,
            user.vip_expire_date || null
          ]
        )

        console.log(`✅ 添加用户: ${user.email} (${user.role})`)
      } catch (error) {
        console.error(`❌ 添加用户 ${user.email} 失败:`, error.message)
      }
    }

    // 显示用户统计
    const [statsRows] = await connection.execute(`
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN is_vip = true THEN 1 ELSE 0 END) as vip_count,
        SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as admin_count
      FROM users
    `)

    const stats = statsRows[0]
    console.log('\n📊 用户统计:')
    console.log(`   总用户数: ${stats.total}`)
    console.log(`   VIP用户: ${stats.vip_count}`)
    console.log(`   管理员: ${stats.admin_count}`)

    console.log('\n🎉 数据库初始化完成！')
  } catch (error) {
    console.error('❌ 数据库操作失败:', error.message)
    if (error.code) {
      console.error('错误代码:', error.code)
    }
  } finally {
    if (connection) {
      await connection.end()
    }
  }
}

// 运行脚本
testAndInitDatabase()
